{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "å\n",
    "sc = (SparkSession.builder.appName('app17-2') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "jmcust = sc.read.table('sqlzoo.jmcust')\n",
    "dressmaker = sc.read.table('sqlzoo.dressmaker')\n",
    "dress_order = sc.read.table('sqlzoo.dress_order')\n",
    "construction = sc.read.table('sqlzoo.construction')\n",
    "quantities = sc.read.table('sqlzoo.quantities')\n",
    "order_line = sc.read.table('sqlzoo.order_line')\n",
    "garment = sc.read.table('sqlzoo.garment')\n",
    "material = sc.read.table('sqlzoo.material')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "Assuming that any garment could be made in any of the available materials, list the garments (description, fabric, colour and pattern) which are expensive to make, that is, those for which the labour costs are 80% or more of the total cost."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>fabric</th>\n",
       "      <th>colour</th>\n",
       "      <th>pattern</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Red/Orange</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Red/Orange</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             description                fabric                colour  \\\n",
       "0   Trousers              Cotton                Yellow Stripe          \n",
       "1   Long Skirt            Cotton                Yellow Stripe          \n",
       "2   Short Skirt           Cotton                Yellow Stripe          \n",
       "3   Sundress              Cotton                Yellow Stripe          \n",
       "4   Trousers              Cotton                Green Stripe           \n",
       "5   Long Skirt            Cotton                Green Stripe           \n",
       "6   Short Skirt           Cotton                Green Stripe           \n",
       "7   Sundress              Cotton                Green Stripe           \n",
       "8   Trousers              Cotton                Black Dotted           \n",
       "9   Long Skirt            Cotton                Black Dotted           \n",
       "10  Short Skirt           Cotton                Black Dotted           \n",
       "11  Sundress              Cotton                Black Dotted           \n",
       "12  Trousers              Cotton                Red Stripe             \n",
       "13  Long Skirt            Cotton                Red Stripe             \n",
       "14  Short Skirt           Cotton                Red Stripe             \n",
       "15  Sundress              Cotton                Red Stripe             \n",
       "16  Trousers              Polyester             Pale Yellow            \n",
       "17  Long Skirt            Polyester             Pale Yellow            \n",
       "18  Short Skirt           Polyester             Pale Yellow            \n",
       "19  Sundress              Polyester             Pale Yellow            \n",
       "20  Trousers              Cotton                Blue Stripe            \n",
       "21  Long Skirt            Cotton                Blue Stripe            \n",
       "22  Short Skirt           Cotton                Blue Stripe            \n",
       "23  Sundress              Cotton                Blue Stripe            \n",
       "24  Trousers              Cotton                Pink Check             \n",
       "25  Long Skirt            Cotton                Pink Check             \n",
       "26  Short Skirt           Cotton                Pink Check             \n",
       "27  Sundress              Cotton                Pink Check             \n",
       "28  Trousers              Rayon                 Red/Orange             \n",
       "29  Sundress              Rayon                 Red/Orange             \n",
       "30  Trousers              Cotton                Blue Abstract          \n",
       "31  Long Skirt            Cotton                Blue Abstract          \n",
       "32  Short Skirt           Cotton                Blue Abstract          \n",
       "33  Sundress              Cotton                Blue Abstract          \n",
       "34  Trousers              Cotton                Green Abstract         \n",
       "35  Long Skirt            Cotton                Green Abstract         \n",
       "36  Short Skirt           Cotton                Green Abstract         \n",
       "37  Sundress              Cotton                Green Abstract         \n",
       "\n",
       "                 pattern  \n",
       "0   Woven                 \n",
       "1   Woven                 \n",
       "2   Woven                 \n",
       "3   Woven                 \n",
       "4   Woven                 \n",
       "5   Woven                 \n",
       "6   Woven                 \n",
       "7   Woven                 \n",
       "8   Woven                 \n",
       "9   Woven                 \n",
       "10  Woven                 \n",
       "11  Woven                 \n",
       "12  Woven                 \n",
       "13  Woven                 \n",
       "14  Woven                 \n",
       "15  Woven                 \n",
       "16  Printed               \n",
       "17  Printed               \n",
       "18  Printed               \n",
       "19  Printed               \n",
       "20  Woven                 \n",
       "21  Woven                 \n",
       "22  Woven                 \n",
       "23  Woven                 \n",
       "24  Woven                 \n",
       "25  Woven                 \n",
       "26  Woven                 \n",
       "27  Woven                 \n",
       "28  Printed               \n",
       "29  Printed               \n",
       "30  Printed               \n",
       "31  Printed               \n",
       "32  Printed               \n",
       "33  Printed               \n",
       "34  Printed               \n",
       "35  Printed               \n",
       "36  Printed               \n",
       "37  Printed               "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(garment.join(material)\n",
    " .filter(col('labour_cost')/(col('labour_cost')+col('cost'))>0.8) \n",
    " .select('description', 'fabric', 'colour', 'pattern')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "List the descriptions and the number of orders of the less popular garments, that is those for which less than the average number of orders per garment have been placed. Also print out the average number of orders per garment. When calculating the average, ignore any garments for which no orders have been made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>n_orders</th>\n",
       "      <th>avg_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>5</td>\n",
       "      <td>5.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>5</td>\n",
       "      <td>5.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Suntop</td>\n",
       "      <td>4</td>\n",
       "      <td>5.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shorts</td>\n",
       "      <td>5</td>\n",
       "      <td>5.166667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            description  n_orders  avg_orders\n",
       "0  Sundress                     5    5.166667\n",
       "1  Short Skirt                  5    5.166667\n",
       "2  Suntop                       4    5.166667\n",
       "3  Shorts                       5    5.166667"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(garment.join(order_line, on=(garment['style_no']==order_line['ol_style']))\n",
    " .groupBy('style_no', 'description')\n",
    " .agg(count('order_ref').alias('n_orders'))\n",
    " .withColumn('avg_orders', avg(col('n_orders')).over(Window.partitionBy(lit(0))))\n",
    " .filter(col('n_orders') < col('avg_orders'))\n",
    " .select('description', 'n_orders', 'avg_orders')\n",
    " .toPandas()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Which is the most popular line, that is, the garment with the highest number of orders. Bearing in mind the fact that there may be several such garments, list the garment description(s) and number(s) of orders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>n_orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Trousers</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            description  n_orders\n",
       "0  Trousers                     6\n",
       "1  Long Skirt                   6"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(garment.join(order_line, on=(garment['style_no']==order_line['ol_style']))\n",
    " .groupBy('style_no', 'description')\n",
    " .agg(count('order_ref').alias('n_orders'))\n",
    " .withColumn('max_orders', max('n_orders').over(Window.partitionBy(lit(0))))\n",
    " .filter(col('n_orders')==col('max_orders'))\n",
    " .select('description', 'n_orders')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "List the descriptions, and costs of the more expensive size 8, Cotton garments which might be ordered, that is those costing more than the average (labour costs + material costs) to make."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>description</th>\n",
       "      <th>material_no</th>\n",
       "      <th>tot_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sundress</td>\n",
       "      <td>14</td>\n",
       "      <td>31.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            description  material_no  tot_cost\n",
       "0  Sundress                       14      31.2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(garment.join(order_line, on=(garment['style_no']==order_line['ol_style']))\n",
    " .join(material, on=(order_line['ol_material']==material['material_no']))\n",
    " .join(quantities, on=((order_line['ol_size']==quantities['size_q']) &\n",
    "                       (order_line['ol_style']==quantities['style_q'])))\n",
    " .withColumn('tot_cost', col('labour_cost') + col('quantity') * col('cost'))\n",
    " .withColumn('avg_cost', avg(col('tot_cost')).over(Window.partitionBy(lit(0))))\n",
    " .filter((col('ol_size') == 8) & (lower(trim(col('fabric'))) == 'cotton') &\n",
    "         (col('tot_cost') > col('avg_cost')))\n",
    " .select('description', 'material_no', 'tot_cost')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "What is the most common size ordered for each garment type? List description, size and number of orders, assuming that there could be several equally popular sizes for each type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>style_no</th>\n",
       "      <th>description</th>\n",
       "      <th>ol_size</th>\n",
       "      <th>max(n_orders)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4</td>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>Short Skirt</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5</td>\n",
       "      <td>Sundress</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5</td>\n",
       "      <td>Sundress</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5</td>\n",
       "      <td>Sundress</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5</td>\n",
       "      <td>Sundress</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>6</td>\n",
       "      <td>Suntop</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>6</td>\n",
       "      <td>Suntop</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6</td>\n",
       "      <td>Suntop</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    style_no           description  ol_size  max(n_orders)\n",
       "0          1  Trousers                    8              3\n",
       "1          1  Trousers                   14              1\n",
       "2          1  Trousers                   16              1\n",
       "3          1  Trousers                   18              1\n",
       "4          2  Long Skirt                  8              3\n",
       "5          2  Long Skirt                 14              1\n",
       "6          2  Long Skirt                 16              1\n",
       "7          2  Long Skirt                 18              1\n",
       "8          3  Shorts                      8              1\n",
       "9          3  Shorts                     10              1\n",
       "10         3  Shorts                     14              1\n",
       "11         3  Shorts                     16              1\n",
       "12         3  Shorts                     18              1\n",
       "13         4  Short Skirt                 8              1\n",
       "14         4  Short Skirt                10              2\n",
       "15         4  Short Skirt                12              1\n",
       "16         4  Short Skirt                16              1\n",
       "17         5  Sundress                    8              1\n",
       "18         5  Sundress                   10              2\n",
       "19         5  Sundress                   12              1\n",
       "20         5  Sundress                   16              1\n",
       "21         6  Suntop                      8              1\n",
       "22         6  Suntop                     10              1\n",
       "23         6  Suntop                     12              2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(garment.join(order_line, on=(garment['style_no']==order_line['ol_style']))\n",
    " .groupBy('style_no', 'description', 'ol_size')\n",
    " .agg(count('order_ref').alias('n_orders'))\n",
    " .groupBy('style_no', 'description', 'ol_size')\n",
    " .max('n_orders')\n",
    " .orderBy('style_no', 'description', 'ol_size')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
